﻿using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;
using System.Web;

namespace T04WebService
{
    public class NewsDao
    {
        // //MySQL的连接串描述
        private static string connStr
            = "server=localhost;user=root;password=;database=test;sslmode=none;";

        /// <summary>
        /// 
        /// </summary>
        /// <param name="num"></param>
        /// <param name="category"></param>
        /// <returns></returns>
        public static List<News> LoadData(int num,int category)
        {
            List<News> newsList = new List<News>();
            MySqlConnection conn = new MySqlConnection(connStr);
            try
            {
                conn.Open();
                //模糊查询
                string sql = "SELECT * FROM tb_news WHERE category=@category ORDER BY publish_time DESC LIMIT 0,@num";
                MySqlCommand cmd = new MySqlCommand(sql, conn);
                //安全的参数(占位符）
                cmd.Parameters.AddWithValue("@num", num);
                cmd.Parameters.AddWithValue("@category", category);
                MySqlDataReader rdr = cmd.ExecuteReader();
                //4.依次访问数据记录并加入数据集合
                while (rdr.Read())
                {
                    News news = new News();
                    news.Id = Convert.ToInt64(rdr["id"]);
                    news.Title= Convert.ToString(rdr["title"]);
                    news.Content = Convert.ToString(rdr["content"]);
                    news.PublishTime = Convert.ToDateTime(rdr["publish_time"]);
                    news.Url = Convert.ToString(rdr["url"]);
                    news.Category = Convert.ToInt32(rdr["category"]);
                    newsList.Add(news);
                }
                //5.关闭游标
                rdr.Close();
            }
            catch (Exception ex)
            {
                Debug.WriteLine(ex.ToString());
            }
            finally
            {
                if (conn != null)
                    conn.Close();
            }
            //throw new NotImplementException();
            Debug.WriteLine(newsList.Count);
            return newsList;
        }
    }
}